# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author:
#        Email:
#     HomePage:
#      Version:
#   LastChange:
#      History:
# =============================================================================
import pymysql

from utils.log_helper import LogHelper
from utils.mysql_server import MySQLServer

logger = LogHelper.get_logger()


class SourceServer(object):
    def __init__(self, mysql_server: MySQLServer,
                 table_name,
                 data_condition,
                 max_query_seconds=100,
                 max_key_value="AUTO",
                 min_key_value="AUTO"):
        self.mysql_server = mysql_server
        self.database_name = self.mysql_server.database_name
        self.table_name = table_name
        self.data_condition = data_condition
        self.max_key_value = max_key_value
        self.min_key_value = min_key_value
        self.max_query_seconds = max_query_seconds
        self.primary_key_name = ""
        self.primary_key_type = ""

    def get_table_columns(self):
        """
        获取列信息
        :return:
        """
        sql_script = """ DESC `{database_name}`.`{table_name}` """.format(
            database_name=self.mysql_server.database_name,
            table_name=self.table_name
        )
        return self.mysql_server.mysql_query(sql_script=sql_script, return_dict=True)

    def get_full_key_range(self):
        """
        获取整表得最大最小值作为结转范围
        :return:
        """
        sql_script = """
SELECT
MAX(`{primary_key_name}`) AS max_key_value,
MIN(`{primary_key_name}`) AS min_key_value
FROM `{database_name}`.`{table_name}`;
""".format(database_name=self.database_name,
           table_name=self.table_name,
           primary_key_name=self.primary_key_name)
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            max_execute_seconds=self.max_query_seconds,
            return_dict=True
        )
        return query_result[0]["max_key_value"], query_result[0]["min_key_value"]

    def get_auto_key_range(self):
        """
        按照结转条件获取最大最小值作为结转范围
        :return:
        """
        sql_script = """
SELECT
MAX(`{primary_key_name}`) AS max_key_value,
MIN(`{primary_key_name}`) AS min_key_value
FROM `{database_name}`.`{table_name}`
WHERE {data_condition};
""".format(database_name=self.database_name,
           table_name=self.table_name,
           primary_key_name=self.primary_key_name,
           data_condition=self.data_condition)
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            max_execute_seconds=self.max_query_seconds,
            return_dict=True
        )
        return query_result[0]["max_key_value"], query_result[0]["min_key_value"]

    def get_loop_key_range(self):
        """
        按照传入的表获取要删除数据最大ID、最小ID、删除总行数
        :return: 返回要删除数据最大ID、最小ID、删除总行数
        """
        if (self.max_key_value == "MAX") and (self.min_key_value == "MIN"):
            self.max_key_value, self.min_key_value = self.get_full_key_range()
        elif (self.max_key_value == "AUTO") and (self.min_key_value == "AUTO"):
            self.max_key_value, self.min_key_value = self.get_auto_key_range()
        if self.max_key_value is None or self.min_key_value is None:
            return None, None
        if self.primary_key_type == "INT":
            return int(self.max_key_value), int(self.min_key_value)
        else:
            return str(self.max_key_value.encode('utf8')), str(self.min_key_value.encode('utf8'))

    def get_next_loop_key(self, current_key_value, batch_scan_rows):
        sql_script = """
SELECT MAX(`{primary_key_name}`) AS max_key_value FROM (
SELECT `{primary_key_name}`
FROM `{database_name}`.`{table_name}`
WHERE {primary_key_name} >'{current_key_value}'
ORDER BY `{primary_key_name}` ASC
LIMIT {batch_scan_rows}
) AS T1;
""".format(database_name=self.database_name,
           table_name=self.table_name,
           primary_key_name=self.primary_key_name,
           current_key_value=pymysql.escape_string(str(current_key_value)),
           batch_scan_rows=batch_scan_rows)
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            sql_paras=None,
            return_dict=True
        )
        if len(query_result) > 0:
            if self.primary_key_type == "INT":
                return query_result[0]["max_key_value"]
            else:
                return str(query_result[0]["max_key_value"].encode('utf8'))
        else:
            return None

    def delete_data_by_keys(self, key_items):
        if len(key_items) == 0:
            return 0
        where_keys = ",".join(list(map(lambda key: "%s", key_items)))
        sql_script = """
DELETE FROM `{database_name}`.`{table_name}` 
WHERE `{primary_key_name}` IN ({where_keys});
""".format(
            database_name=self.database_name,
            table_name=self.table_name,
            primary_key_name=self.primary_key_name,
            where_keys=where_keys
        )
        affect_rows = self.mysql_server.mysql_exec(
            sql_script=sql_script,
            sql_paras=key_items
        )
        logger.info("在源头实例上清理记录: {}".format(affect_rows))
        return affect_rows

    def get_data_by_keys(self, key_items):
        """
        获取特定可以的数据
        :param key_items:
        :return:
        """
        if len(key_items) == 0:
            return []
        where_keys = ",".join(list(map(lambda key: "%s", key_items)))
        sql_script = """
SELECT * 
FROM `{database_name}`.`{table_name}` 
WHERE `{primary_key_name}` IN ({where_keys})
""".format(
            database_name=self.database_name,
            table_name=self.table_name,
            primary_key_name=self.primary_key_name,
            where_keys=where_keys
        )
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            sql_paras=key_items,
            return_dict=True
        )
        logger.info("在源头实例上找到记录: {}".format(len(query_result)))
        return query_result

    def get_new_key_items(self, key_items):
        """
        获取特定可以的数据
        :param key_items:
        :return:
        """
        if len(key_items) == 0:
            return []
        where_keys = ",".join(list(map(lambda key: "%s", key_items)))
        sql_script = """
SELECT `{primary_key_name}` 
FROM `{database_name}`.`{table_name}` 
WHERE `{primary_key_name}` IN ({where_keys})
""".format(
            database_name=self.database_name,
            table_name=self.table_name,
            primary_key_name=self.primary_key_name,
            where_keys=where_keys
        )
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            sql_paras=key_items,
            return_dict=True
        )
        return list(map(lambda key: key[self.primary_key_name], query_result))

    def get_key_items(self, current_key_value, next_key_value, max_key_value):
        """
        获取指定区间的主键值
        :param current_key_value:
        :param next_key_value:
        :param max_key_value:
        :return:
        """
        if next_key_value == max_key_value:
            sql_script = """
            SELECT `{primary_key_name}` 
            FROM `{database_name}`.`{table_name}` AS source_table
            WHERE ({data_condition})
            AND source_table.`{primary_key_name}`>='{current_key_value}'
            AND source_table.`{primary_key_name}`<='{next_key_value}'
            """
        else:
            sql_script = """
            SELECT `{primary_key_name}` 
            FROM `{database_name}`.`{table_name}` AS source_table
            WHERE ({data_condition})
            AND source_table.`{primary_key_name}`>='{current_key_value}'
            AND source_table.`{primary_key_name}`<'{next_key_value}'
            """
        sql_script = sql_script.format(
            database_name=self.database_name,
            table_name=self.table_name,
            data_condition=self.data_condition,
            primary_key_name=self.primary_key_name,
            current_key_value=pymysql.escape_string(str(current_key_value)),
            next_key_value=pymysql.escape_string(str(next_key_value))
        )
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script, sql_paras=None, return_dict=True
        )
        return list(map(lambda key: key[self.primary_key_name], query_result))

    def get_create_table_script(self):
        """
        获取特定表的建表语句
        :return:
        """
        sql_script = "show create table `{}`.`{}`".format(
            self.database_name,
            self.table_name
        )
        query_result = self.mysql_server.mysql_query(
            sql_script=sql_script,
            return_dict=False
        )
        if len(query_result) > 0:
            return query_result[0][1]
        else:
            return None
